package model

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource
import wangzx.scala_commons.sql._

import scala.collection.mutable.ListBuffer

/**
  * author: HuaZhe Ray
  *
  * describe: TODO
  *
  * createDate: 2017/12/11
  * createTime: 19:19
  *
  */
object JdbcRepo {

  val dataSource = {

    val ds = new MysqlDataSource
    ds.setURL(s"jdbc:mysql://127.0.0.1/goods_db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull")
    ds.setUser("root")
    ds.setPassword("123456")
    ds
  }

  /**
    * 不合适的写法
    * 1.尝试消除掉 new ArrayBuffer[String] ， arrayBuffer ++= re这类的 可变操作。 尽量使用 filter/map等替代。
    *
    * @return
    */
  def queryStudentsNotGood(): List[Student] = {
    val rows = dataSource.rows[StudentWrapper]("select * from student")
    val listBuffer = new ListBuffer[Student]
    rows.foreach(x => listBuffer.append(Student(x.name, x.sex, Lesson("语文", x.chinese), Lesson("数学", x.math), Lesson("英语", x.english))))
    val students = listBuffer.toList
    students
  }


  /**
    * 推荐的写法
    *
    * @return
    */
  def queryStudents(): List[Student] = {
    dataSource.rows[StudentWrapper]("select * from student")
      .map(x => Student(x.name, x.sex, Lesson("语文", x.chinese), Lesson("数学", x.math), Lesson("英语", x.english)))
  }


  /**
    * 查询出所有的年级，和年级包含的班级， 并转化为 model
    *
    * @return
    */
  def queryGrades(): List[Grade] = {
    val rows = dataSource.rows[Row](
      """SELECT g.`name` AS g_name,c.`name` AS c_name FROM
                                          grade_clazz gc LEFT JOIN grade g ON  g.`id` = gc.`grade_id`
                                          LEFT JOIN clazz c ON gc.`clazz_id` = c.`id`""")
    val res = rows.groupBy(_.cells(0).value)
    val res1 = res.map(x => Grade(x._1.toString, x._2.map(x => Classes(x.cell(2).value.toString, Nil))))
    //        val res1 = res.map(x=> x._2.map(_.cell(2).value))
    res1.toList
  }

  /**
    * 根据班级名称查询出班级学生
    *
    * @param clazz
    * @return
    */
  def queryStudentsByClass(clazz: Classes): List[Student] = {
    val rows = dataSource.rows[StudentWrapper](
      s"""SELECT s.`name`,s.`sex`,s.`chinese`,s.`math`,s.`english` FROM clazz_student cs
                                   LEFT JOIN clazz c ON cs.`clazz_id` = c.`id` LEFT JOIN student s ON cs.`stu_id`=s.`id`
                                   WHERE c.`name` = '${clazz.name}'""")
    val res = rows.map(x => Student(x.name, x.sex, Lesson("语文", x.chinese), Lesson("数学", x.math), Lesson("英语", x.english)))
    res
  }


}
